import time
import datetime
import json
import requests
import pandas as pd
import pymysql
import copy
from email.mime.text import MIMEText
from email.header import Header
from email.mime.multipart import MIMEMultipart
import smtplib
import logging
import shutil
import os

os.mkdir("test")
ecu_text = ecu_text
start_time_text = start_time_text
end_time_text = end_time_text
env_type_text = env_type_text
email_name_text = email_name_text
get_type = get_type_text


class GetStressData:
    def __init__(self, date1, date2, ecu, env):
        self.request_dict = {}
        self.response_dict = {}
        self.data_dict = {}
        self.count_data_dict = {}
        self.count_200 = []
        self.count_204 = []
        self.count_0 = []
        self.count_others = []
        self.count_success_res = []
        self.count_success = []
        self.count_mean_ts = []
        self.count_command = [301, 302, 304, 305, 306, 307, 351,345,346,347,376,377]
        self.count_type = ["远程开锁", "远程撤防", "远程关锁", "打开电池锁", "关闭电池锁", "远程寻车", "重置蓝牙密码", "开头盔锁", "关头盔锁", "开头盔锁并自动回位", "头盔借用", "头盔归还"]
        self.count_command_2 = [301, 302, 304, 305, 306, 307, 351, 345, 346, 347, 343, 344, 374, 375, 376, 377]
        self.count_type_2 = ["远程开锁", "远程撤防", "远程关锁", "打开电池锁", "关闭电池锁", "远程寻车", "重置蓝牙密码", "开头盔锁", "关头盔锁", "开头盔锁自动回位",
                             "开后轮锁", "关后轮锁", "头盔车辆开启", "头盔车辆关闭", "头盔借用", "头盔归还"]
        self.start_time = date1
        self.end_time = date2
        self.esn_list = ecu.replace(" ","").split(",")
        self.env_type = env

        self.start_time = datetime.datetime.strptime(self.start_time, "%Y-%m-%d %H:%M:%S")
        now_bef_start = (self.start_time - datetime.timedelta(hours=8)).strftime("%Y-%m-%d %H:%M:%S")
        self.start_time = now_bef_start.replace(" ", "T") + ".000Z"

        self.end_time = datetime.datetime.strptime(self.end_time, "%Y-%m-%d %H:%M:%S")
        now_bef_end = (self.end_time - datetime.timedelta(hours=8)).strftime("%Y-%m-%d %H:%M:%S")
        self.end_time = now_bef_end.replace(" ", "T") + ".000Z"

    # 接口请求
    def get_json(self, time_linetypes, ecuSn, timeline_start_time, timeline_end_time, req_scroll_id, cmd, env_type):
        query = {
            "params": {
                "bike_sns": [],
                "bike_sns_str": "",
                "cmd": cmd,
                "cmd_code": "",
                "ecu_sn": ecuSn,
                "gsm_signal_strength": "",
                "page_size": "1000",
                "parse_able": True,
                "scroll_id": req_scroll_id,
                "standby_battery_capacity": "",
                "time_linetypes": [time_linetypes],
                "timeline_start_time": timeline_start_time,
                "timeline_end_time": timeline_end_time,
            }
        }
        datas = json.dumps(query)
        if env_type == "test":
            self.timeline_url = "http://test-os-gateway.songguo7.com/api/iot-timeline/timeline/list"
        elif env_type == "online":
            self.timeline_url = "http://os-gateway.songguo7.com/api/iot-timeline/timeline/list"
        headers = {
            "X-AUTH-SOURCE": "SSO",
            "Cookie": self.get_token(env_type),
            "Content-Type": "application/json; charset=UTF-8",
            'Connection': 'close'
        }
        response = requests.post(url=self.timeline_url, data=datas,
                                 headers=headers, timeout=None)
        data = response.json()
        if "error" in data.keys():
            self.update_token(env_type)
            headers = {
                "X-AUTH-SOURCE": "SSO",
                "Cookie": self.get_token(env_type),
                "Content-Type": "application/json; charset=UTF-8",
                'Connection': 'close'
            }
            response = requests.post(url=self.timeline_url, data=datas,
                                     headers=headers, timeout=None)
            data = response.json()["data"]
            time_line_record_list = data["time_line_record_entities"]
            res_scroll_id = data["scroll_id"]
            return time_line_record_list, res_scroll_id
        else:
            data = data["data"]
            time_line_record_list = data["time_line_record_entities"]
            res_scroll_id = data["scroll_id"]
            return time_line_record_list, res_scroll_id

    def get_token(self, env_type):
        db = pymysql.connect(host="10.100.73.74", port=3306, user="ttyc_57", password="OClgaa6YD!Rylnb5",
                             db="test_iot")
        cursor = db.cursor()
        sql = "SELECT * FROM test_iot.name_value where name = '{}_token'".format(env_type)
        cursor.execute(sql)
        data = cursor.fetchall()
        token = data[0][-1]
        db.close()
        cursor.close()
        return token

    def update_token(self, env_type):
        db = pymysql.connect(host="10.100.73.74", port=3306, user="ttyc_57", password="OClgaa6YD!Rylnb5",
                             db="test_iot")
        cursor = db.cursor()
        sql = '''UPDATE test_iot.name_value SET value = "''' + self.get_cookie(
            env_type) + '''" WHERE (name = '{}_token');'''.format(env_type)
        cursor.execute(sql)
        db.commit()
        db.close()
        cursor.close()

    def get_cookie(self, env_type):
        if env_type == "test":
            self.url = "http://test-sso-in.songguo7.com/sso/login"
            self.params = {
                "params": {
                    "login_name": "xingfengye",
                    "password": "e590ab8e56e8133aa5fb99e3f2ec9d4adad509fe"
                }
            }
        elif env_type == "online":
            self.url = "https://sso.songguo7.com/sso/login"
            self.params = {
                "params": {
                    "login_name": "xingfengye",
                    "password": "8197548f01ba4da1881ce3a8c76a24d1b7a381be"
                }
            }
        res = requests.post(self.url, json=self.params)
        cookie = res.json()["data"]["access_token"]
        cookies = copy.deepcopy(cookie)
        return "sandbox_access_token=" + cookies + "; access_token=" + cookies

    # 由于接口一次只能返1000的数据，故多次请求累加至字典中
    def count_request_dict(self, req_list):
        for i in req_list:
            try:
                command_code = json.loads(i["ext"])["code"]
                request_msg_id = json.loads(i["ext"])["msg_id"]
                ts = json.loads(i["ext"])["ts"]
                esn = json.loads(i["ext"])["esn"]
                self.request_dict[request_msg_id] = [command_code, esn, ts]
            except:
                pass

    # 请求字典["msg_id": ["command_code", "esn", "ts"]]
    def get_request_dict(self, esn):
        requests_list = self.get_json(10, esn, self.start_time,
                                      self.end_time, "", "", self.env_type)
        data_list, res_scroll_id = requests_list
        while 1:
            if res_scroll_id != "end":
                self.count_request_dict(data_list)
                requests_list = self.get_json(10, esn, self.start_time,
                                              self.end_time, res_scroll_id, "", self.env_type)
                data_list, res_scroll_id = requests_list
            elif res_scroll_id == "end":
                self.count_request_dict(data_list)
                break
        return self.request_dict

    # 由于接口一次只能返1000的数据，故多次请求累加至字典中
    def count_response_dict(self, req_list):
        for i in req_list:
            # status_code = json.loads(i["ext"])["data"]["status"]
            status_code = json.loads(i["ext"])["data"]["payload"]["result"]
            response_msg_id = json.loads(i["ext"])["data"]["msg_id"]
            # 取得最外面的ts
            try:
                ts = json.loads(i["ext"])["ts"]
            except:
                ts = "None"
            self.response_dict[response_msg_id] = [status_code, ts]

    # 响应字典["msg_id": ["status_code", "ts"]]
    def get_response_dict(self, esn):
        requests_list = self.get_json(20, esn, self.start_time,
                                      self.end_time, "", "", self.env_type)
        data_list, res_scroll_id = requests_list
        while 1:
            if res_scroll_id != "end":
                self.count_response_dict(data_list)
                requests_list = self.get_json(20, esn, self.start_time,
                                              self.end_time, res_scroll_id, "", self.env_type)
                data_list, res_scroll_id = requests_list
            elif res_scroll_id == "end":
                self.count_response_dict(data_list)
                break
        return self.response_dict

    # 创建excel字典，加入"esn"、"command"、"status"、"request_ts"、"response_ts"、"diff_ts"的key及value
    def get_excel_dict(self, esn):
        response_dict = self.get_response_dict(esn)
        request_dict = self.get_request_dict(esn)
        command_list = [i[0] for i in request_dict.values()]
        esn_list = [i[1] for i in request_dict.values()]
        request_ts = [i[2] for i in request_dict.values()]
        self.data_dict["esn"] = esn_list
        self.data_dict["command"] = command_list
        # 判断请求是否有响应，有响应填对应"status_code"，无响应则填0
        status_list = []
        response_ts = []
        diff_ts = []
        for i in request_dict.items():
            if i[0] in response_dict.keys():
                status_list.append(response_dict[i[0]][0])
                response_ts.append(response_dict[i[0]][1])
                # if response_dict[i[0]][1] == "None":
                #     diff_ts.append("None")
                # else:
                diff_ts.append(abs((response_dict[i[0]][1] - i[1][2]) / 1000))
            else:
                status_list.append(255)
                response_ts.append("None")
                diff_ts.append("None")

        self.data_dict["status"] = status_list
        request_ts = [time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(int(i / 1000))) if i != "None" else "None" for i
                      in request_ts]
        response_ts = [time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(int(i / 1000))) if i != "None" else "None" for
                       i
                       in response_ts]
        self.data_dict["request_ts"] = request_ts
        self.data_dict["response_ts"] = response_ts
        self.data_dict["diff_ts"] = diff_ts

        return self.data_dict

    def get_firmware_version_and_product_name(self, ste_sn):
        query = {
            "params": {
                "ste_sn": ste_sn,
                "scopes": [
                    "BASE_REPORT",
                    "STE_INFO",
                    "BIKE_STATUS",
                    "BATTERY_STATUS",
                    "GPS_STATUS"
                ]
            }
        }
        datas = json.dumps(query)
        if self.env_type == "test":
            self.query_url = "http://test-os-gateway.songguo7.com/api/iot-report-web/report/parsed/last/get"
        elif self.env_type == "online":
            self.query_url = "http://os-gateway.songguo7.com/api/iot-report-web/report/parsed/last/get"
        headers = {
            "X-AUTH-SOURCE": "SSO",
            "Cookie": self.get_token(self.env_type),
            "Content-Type": "application/json; charset=UTF-8",
            'Connection': 'close'
        }
        response = requests.post(url=self.query_url, data=datas, headers=headers)
        firmware_version = response.json()["data"]["ste_info"]["firmware_version"]
        product_name = response.json()["data"]["ste_info"]["product_name"]
        return firmware_version, product_name

    def write_excel_pd(self):
        self.update_token(self.env_type)
        writer = pd.ExcelWriter("test/压测原始数据.xls")
        for i in self.esn_list:
            df = pd.DataFrame(self.get_excel_dict(i))
            self.data_dict = {}
            self.request_dict = {}
            self.response_dict = {}
            df = df.set_index("esn")
            df.to_excel(excel_writer=writer, sheet_name=i)

        writer.save()
        writer.close()

    def statistical_account(self):
        ecu_data_list = []
        firmware_version_data_list = []
        command_data_list = []
        command_type_data_list = []

        for ecu in self.esn_list:
            firmware_version, product_name = self.get_firmware_version_and_product_name(ecu)
            if product_name == "DHF" or product_name == "FMT" or product_name == "BTH":
                self.deal_every_ecu(self.count_command_2, ecu)
                c = 0
                for i in range(len(self.count_command_2)):
                    command_data_list.append(self.count_command_2[i])
                    command_type_data_list.append(self.count_type_2[i])
                while c < len(self.count_command_2):
                    ecu_data_list.append(str(ecu))
                    firmware_version_data_list.append(firmware_version)
                    c += 1
            else:
                self.deal_every_ecu(self.count_command, ecu)
                c = 0
                for i in range(len(self.count_command)):
                    command_data_list.append(self.count_command[i])
                    command_type_data_list.append(self.count_type[i])
                while c < len(self.count_command):
                    ecu_data_list.append(str(ecu))
                    firmware_version_data_list.append(firmware_version)
                    c += 1

        self.count_data_dict["ECU编号"] = ecu_data_list
        self.count_data_dict["固件版本"] = firmware_version_data_list
        self.count_data_dict["命令"] = command_data_list
        self.count_data_dict["类型"] = command_type_data_list

        # self.count_data_dict["成功200"] = self.count_200
        # self.count_data_dict["超时204"] = self.count_204
        # self.count_data_dict["未响应0"] = self.count_0
        self.count_data_dict["成功0"] = self.count_200
        self.count_data_dict["失败1"] = self.count_204
        self.count_data_dict["未响应255"] = self.count_0
        self.count_data_dict["其他"] = self.count_others
        self.count_data_dict["成功率"] = self.count_success
        self.count_data_dict["响应成功率"] = self.count_success_res
        self.count_data_dict["响应时长"] = self.count_mean_ts
        excel_path = 'test/压力测试统计.xls'
        writer = pd.ExcelWriter(excel_path)

        df = pd.DataFrame(self.count_data_dict)
        df = df.set_index("ECU编号")
        df.to_excel(excel_writer=writer)

        writer.save()
        writer.close()

    def deal_every_ecu(self, count_command, ecu):
        df = pd.read_excel('test/压测原始数据.xls', sheet_name=ecu)
        for command in count_command:
            # status_200 = len(df[(df["esn"] == int(ecu)) & (df["status"] == 200) & (df["command"] == command)])
            # status_204 = len(df[(df["esn"] == int(ecu)) & (df["status"] == 204) & (df["command"] == command)])
            status_0 = len(df[(df["esn"] == int(ecu)) & (df["status"] == 0) & (df["command"] == command)])
            status_1 = len(df[(df["esn"] == int(ecu)) & (df["status"] == 1) & (df["command"] == command)])
            status_255 = len(df[(df["esn"] == int(ecu)) & (df["status"] == 255) & (df["command"] == command)])
            status_others = len(df[(df["esn"] == int(ecu)) & (df["status"] != 255) & (df["command"] == command)
                                   & (df["status"] != 0) & (df["status"] != 1)])
            diff_ts = df[(df["esn"] == int(ecu)) & (df["status"] != 255) & (df["command"] == command)]["diff_ts"].mean()
            # diff_ts = df[((df["esn"] == int(ecu)) & (df["status"] == 0) & (df["command"] == command)) ^ ((df["esn"]
            # == int(ecu)) & (df["status"] == 1) & (df["command"] == command))]["diff_ts"].mean()
            # self.count_200.append(status_200) self.count_204.append(status_204)
            self.count_200.append(status_0)
            self.count_204.append(status_1)
            self.count_0.append(status_255)
            self.count_others.append(status_others)
            self.count_mean_ts.append(diff_ts)

            try:
                # success_acc = str(round((status_200 / (status_200 + status_204 + status_0)) * 100, 4)) + "%"
                success_acc = str(round((status_0 / (status_0 + status_1 + status_255 + status_others)) * 100, 4)) + "%"
                self.count_success.append(success_acc)
            except:
                self.count_success.append(0)

            try:
                success_res = str(round(
                    ((status_0 + status_1 + status_others) / (status_0 + status_1 + status_255 + status_others)) * 100,
                    4)) + '%'
                self.count_success_res.append(success_res)
            except:
                self.count_success_res.append(0)


class GetTxtData:
    def __init__(self, date1, date2, ecu, env):
        self.start_time = date1
        self.end_time = date2
        self.esn_list = ecu.replace(" ","").split(",")
        self.env_type = env
        self.request_dict = {}
        self.data_dict = {}

        self.start_time = datetime.datetime.strptime(self.start_time, "%Y-%m-%d %H:%M:%S")
        now_bef_start = (self.start_time - datetime.timedelta(hours=8)).strftime("%Y-%m-%d %H:%M:%S")
        self.start_time = now_bef_start.replace(" ", "T") + ".000Z"

        self.end_time = datetime.datetime.strptime(self.end_time, "%Y-%m-%d %H:%M:%S")
        now_bef_end = (self.end_time - datetime.timedelta(hours=8)).strftime("%Y-%m-%d %H:%M:%S")
        self.end_time = now_bef_end.replace(" ", "T") + ".000Z"

    # 接口请求
    def get_json(self, time_linetypes, ecuSn, timeline_start_time, timeline_end_time, req_scroll_id, cmd, env_type):
        query = {
            "params": {
                "bike_sns": [],
                "bike_sns_str": "",
                "cmd": cmd,
                "cmd_code": "",
                "ecu_sn": ecuSn,
                "gsm_signal_strength": "",
                "page_size": "1000",
                "parse_able": True,
                "scroll_id": req_scroll_id,
                "standby_battery_capacity": "",
                "time_linetypes": [time_linetypes],
                "timeline_start_time": timeline_start_time,
                "timeline_end_time": timeline_end_time,
            }
        }
        datas = json.dumps(query)
        if env_type == "test":
            self.timeline_url = "http://test-os-gateway.songguo7.com/api/iot-timeline/timeline/list"
        elif env_type == "online":
            self.timeline_url = "http://os-gateway.songguo7.com/api/iot-timeline/timeline/list"
        headers = {
            "X-AUTH-SOURCE": "SSO",
            "Cookie": self.get_token(env_type),
            "Content-Type": "application/json; charset=UTF-8",
            'Connection': 'close'
        }
        response = requests.post(url=self.timeline_url, data=datas,
                                 headers=headers, timeout=None)
        data = response.json()
        if "error" in data.keys():
            self.update_token(env_type)
            headers = {
                "X-AUTH-SOURCE": "SSO",
                "Cookie": self.get_token(env_type),
                "Content-Type": "application/json; charset=UTF-8",
                'Connection': 'close'
            }
            response = requests.post(url=self.timeline_url, data=datas,
                                     headers=headers, timeout=None)
            data = response.json()["data"]
            time_line_record_list = data["time_line_record_entities"]
            res_scroll_id = data["scroll_id"]
            return time_line_record_list, res_scroll_id
        else:
            data = data["data"]
            time_line_record_list = data["time_line_record_entities"]
            res_scroll_id = data["scroll_id"]
            return time_line_record_list, res_scroll_id

    def get_token(self, env_type):
        db = pymysql.connect(host="10.100.73.74", port=3306, user="ttyc_57", password="OClgaa6YD!Rylnb5",
                             db="test_iot")
        cursor = db.cursor()
        sql = "SELECT * FROM test_iot.name_value where name = '{}_token'".format(env_type)
        cursor.execute(sql)
        data = cursor.fetchall()
        token = data[0][-1]
        db.close()
        cursor.close()
        return token

    def update_token(self, env_type):
        db = pymysql.connect(host="10.100.73.74", port=3306, user="ttyc_57", password="OClgaa6YD!Rylnb5",
                             db="test_iot")
        cursor = db.cursor()
        sql = '''UPDATE test_iot.name_value SET value = "''' + self.get_cookie(
            env_type) + '''" WHERE (name = '{}_token');'''.format(env_type)
        cursor.execute(sql)
        db.commit()
        db.close()
        cursor.close()

    def get_cookie(self, env_type):
        if env_type == "test":
            self.url = "http://test-sso-in.songguo7.com/sso/login"
            self.params = {
                "params": {
                    "login_name": "xingfengye",
                    "password": "1c4bee00e36c798f84b1444ded8634e98bf53b88"
                }
            }
        elif env_type == "online":
            self.url = "https://sso.songguo7.com/sso/login"
            self.params = {
                "params": {
                    "login_name": "xingfengye",
                    "password": "7d36f9e069818f3b1f68de0fa400436de9492801"
                }
            }
        res = requests.post(self.url, json=self.params)
        cookie = res.json()["data"]["access_token"]
        cookies = copy.deepcopy(cookie)
        return "sandbox_access_token=" + cookies + "; access_token=" + cookies

    # 由于接口一次只能返1000的数据，故多次请求累加至字典中
    def count_request_dict(self, req_list):
        for i in req_list:
            req_time = json.loads(i["ext"])["createTime"]
            longitude = json.loads(i["ext"])["经度"]
            latitude = json.loads(i["ext"])["纬度"]
            self.request_dict[req_time] = [str(longitude) + "|" + str(latitude)]

    # 请求字典["createTime": ["经度", "纬度"]]
    def get_request_dict(self, esn):
        requests_list = self.get_json(30, esn, self.start_time,
                                      self.end_time, "", 2, self.env_type)
        data_list, res_scroll_id = requests_list
        while 1:
            if res_scroll_id != "end":
                self.count_request_dict(data_list)
                requests_list = self.get_json(30, esn, self.start_time,
                                              self.end_time, res_scroll_id, 2, self.env_type)
                data_list, res_scroll_id = requests_list
            elif res_scroll_id == "end":
                self.count_request_dict(data_list)
                break
        return self.request_dict

    def get_excel_dict_and_write_txt(self, esn):
        # 写excel
        self.data_dict["time"] = [time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(i / 1000)) for i in
                                  self.get_request_dict(esn).keys()]
        self.data_dict["经纬度"] = [i[0] for i in self.get_request_dict(esn).values()]
        diff_ts = []
        diff_list = [i for i in self.get_request_dict(esn).keys()]
        for i in range(len(diff_list)):
            try:
                diff_ts.append((diff_list[i] - diff_list[i + 1]) / 1000)
            except:
                diff_ts.append(0)
        self.data_dict["diff_ts"] = diff_ts
        # 写txt
        txt_name = "test/" + esn + ".txt"
        with open(txt_name, "a") as f:
            txt_list = [str(i[0]) for i in self.get_request_dict(esn).values()]
            for i in txt_list:
                if "0.0|0.0" not in i and all([float(x) < 1000 for x in i.split("|")]):
                    f.write(i + ";")
            f.close()
        return self.data_dict

    def write_excel_pd(self):
        self.update_token(self.env_type)
        excel_path = "test/" + self.start_time.split("T")[0] + '.xlsx'
        writer = pd.ExcelWriter(excel_path)
        for i in self.esn_list:
            df = pd.DataFrame(self.get_excel_dict_and_write_txt(i))
            self.request_dict = {}
            self.data_dict = {}
            df = df.set_index("time")
            df.to_excel(excel_writer=writer, sheet_name=i)
        writer.save()
        writer.close()


class GetGPSData:
    def __init__(self, date1, date2, ecu, env):
        self.azimuth_list = []
        self.speed_list = []
        self.data_dict1 = {}
        self.data_dict2 = {}
        self.data_dict3 = {}
        self.data_dict4 = {}
        self.restart_times_list = []
        self.restart_reason0_list = []
        self.restart_reason1_list = []
        self.restart_reason4_list = []
        self.respect_pulse = 0
        self.restart_times = 0
        self.restart_reason0_times = 0
        self.restart_reason1_times = 0
        self.restart_reason4_times = 0
        self.heart_list = []
        self.heart_times = 0
        self.gps_times = 0
        self.gps_list = []
        self.weight_times = 0
        self.weight_list = []
        self.respect_pulse = []
        self.actual_pulse_list = []
        self.latitude_and_longitude_dict = {}
        self.esn_data_list = []
        self.longitude_data_list = []
        self.latitude_data_list = []
        self.req_time_data_list = []
        self.gps_satellite_list = []
        self.gsm_list = []
        self.satellite0_list = []
        self.satellite1_list = []
        self.satellite2_list = []
        self.satellite3_list = []
        self.satellite4_list = []
        self.location_list = []
        self.location_type_list = []
        self.mobile_network_type_list = []
        self.location_0_account = []
        self.location_1_account = []
        self.location_2_account = []
        self.type_2g_account = []
        self.type_4g_account = []
        self.avg_snr1 = []
        self.avg_snr2 = []
        self.avg_snr3 = []
        self.avg_snr4 = []
        self.avg_snr5 = []
        self.avg_gsm = []
        self.avg_snr1_5 = []
        self.avg_lock_star = []
        self.alarm_times = 0
        self.alarm_times_list = []
        self.alarm_340_times = 0
        self.alarm_340_times_list = []

        self.start_time_str = date1
        self.end_time_str = date2
        self.esn_list = ecu.replace(" ","").split(",")
        self.excel_path = "GPS数据.xlsx"
        self.env_type = env
        self.start_time_ts = time.mktime(time.strptime(str(self.start_time_str), "%Y-%m-%d %H:%M:%S"))
        self.end_time_ts = time.mktime(time.strptime(str(self.end_time_str), "%Y-%m-%d %H:%M:%S"))

        self.start_time_str = datetime.datetime.strptime(self.start_time_str, "%Y-%m-%d %H:%M:%S")
        now_bef_start = (self.start_time_str - datetime.timedelta(hours=8)).strftime("%Y-%m-%d %H:%M:%S")
        self.start_time = now_bef_start.replace(" ", "T") + ".000Z"

        self.end_time_str = datetime.datetime.strptime(self.end_time_str, "%Y-%m-%d %H:%M:%S")
        now_bef_end = (self.end_time_str - datetime.timedelta(hours=8)).strftime("%Y-%m-%d %H:%M:%S")
        self.end_time = now_bef_end.replace(" ", "T") + ".000Z"

    def get_json(self, time_linetypes, ecuSn, timeline_start_time, timeline_end_time, req_scroll_id, cmd, env_type):
        query = {
            "params": {
                "bike_sns": [],
                "bike_sns_str": "",
                "cmd": cmd,
                "cmd_code": "",
                "ecu_sn": ecuSn,
                "gsm_signal_strength": "",
                "page_size": "1000",
                "parse_able": True,
                "scroll_id": req_scroll_id,
                "standby_battery_capacity": "",
                "time_linetypes": [time_linetypes],
                "timeline_start_time": timeline_start_time,
                "timeline_end_time": timeline_end_time,
            }
        }
        datas = json.dumps(query)
        if env_type == "test":
            self.timeline_url = "http://test-os-gateway.songguo7.com/api/iot-timeline/timeline/list"
        elif env_type == "online":
            self.timeline_url = "http://os-gateway.songguo7.com/api/iot-timeline/timeline/list"
        headers = {
            "X-AUTH-SOURCE": "SSO",
            "Cookie": self.get_token(env_type),
            "Content-Type": "application/json; charset=UTF-8",
            'Connection': 'close'
        }
        response = requests.post(url=self.timeline_url, data=datas,
                                 headers=headers, timeout=None)
        print(datas)
        print(self.timeline_url)
        
        data = response.json()
        print(data)
        if "error" in data.keys():
            self.update_token(env_type)
            headers = {
                "X-AUTH-SOURCE": "SSO",
                "Cookie": self.get_token(env_type),
                "Content-Type": "application/json; charset=UTF-8",
                'Connection': 'close'
            }
            response = requests.post(url=self.timeline_url, data=datas,
                                     headers=headers, timeout=None)
            data = response.json()["data"]
            time_line_record_list = data["time_line_record_entities"]
            res_scroll_id = data["scroll_id"]
            return time_line_record_list, res_scroll_id
        else:
            data = data["data"]
            time_line_record_list = data["time_line_record_entities"]
            res_scroll_id = data["scroll_id"]
            return time_line_record_list, res_scroll_id

    def get_token(self, env_type):
        db = pymysql.connect(host="10.100.73.74", port=3306, user="ttyc_57", password="OClgaa6YD!Rylnb5",
                             db="test_iot")
        cursor = db.cursor()
        sql = "SELECT * FROM test_iot.name_value where name = '{}_token'".format(env_type)
        cursor.execute(sql)
        data = cursor.fetchall()
        token = data[0][-1]
        db.close()
        cursor.close()
        return token

    def update_token(self, env_type):
        db = pymysql.connect(host="10.100.73.74", port=3306, user="ttyc_57", password="OClgaa6YD!Rylnb5",
                             db="test_iot")
        cursor = db.cursor()
        sql = '''UPDATE test_iot.name_value SET value = "''' + self.get_cookie(
            env_type) + '''" WHERE (name = '{}_token');'''.format(env_type)
        cursor.execute(sql)
        db.commit()
        db.close()
        cursor.close()

    def get_cookie(self, env_type):
        if env_type == "test":
            self.url = "http://test-sso-in.songguo7.com/sso/login"
            self.params = {
                "params": {
                    "login_name": "xingfengye",
                    "password": "1c4bee00e36c798f84b1444ded8634e98bf53b88"
                }
            }
        elif env_type == "online":
            self.url = "https://sso.songguo7.com/sso/login"
            self.params = {
                "params": {
                    "login_name": "xingfengye",
                    "password": "7d36f9e069818f3b1f68de0fa400436de9492801"
                }
            }
        res = requests.post(self.url, json=self.params)
        cookie = res.json()["data"]["access_token"]
        cookies = copy.deepcopy(cookie)
        return "sandbox_access_token=" + cookies + "; access_token=" + cookies

    def count_heart_times(self, req_list):
        self.heart_times += len(req_list)

    def get_heart_times(self, esn):
        requests_list = self.get_json(30, esn, self.start_time,
                                      self.end_time, "", 5, self.env_type)
        data_list, res_scroll_id = requests_list
        while 1:
            if res_scroll_id != "end":
                self.count_heart_times(data_list)
                requests_list = self.get_json(30, esn, self.start_time, self.end_time, res_scroll_id, 5, self.env_type)
                data_list, res_scroll_id = requests_list
            elif res_scroll_id == "end":
                self.count_heart_times(data_list)
                break
        self.heart_list.append(self.heart_times)
        self.heart_times = 0

    def count_gps_times(self, req_list):
        self.gps_times += len(req_list)

    def get_gps_times(self, esn):
        requests_list = self.get_json(30, esn, self.start_time,
                                      self.end_time, "", 2, self.env_type)
        data_list, res_scroll_id = requests_list
        while 1:
            if res_scroll_id != "end":
                self.count_gps_times(data_list)
                requests_list = self.get_json(30, esn, self.start_time,self.end_time, res_scroll_id, 2, self.env_type)
                data_list, res_scroll_id = requests_list
            elif res_scroll_id == "end":
                self.count_gps_times(data_list)
                break
        self.gps_list.append(self.gps_times)
        self.gps_times = 0

    def count_weight_times(self, req_list):
        self.weight_times += len(req_list)

    def get_weight_times(self, esn):
        requests_list = self.get_json(30, esn, self.start_time,
                                      self.end_time, "", 32, self.env_type)
        data_list, res_scroll_id = requests_list
        while 1:
            if res_scroll_id != "end":
                self.count_weight_times(data_list)
                requests_list = self.get_json(30, esn, self.start_time,
                                              self.end_time, res_scroll_id, 32, self.env_type)
                data_list, res_scroll_id = requests_list
            elif res_scroll_id == "end":
                self.count_weight_times(data_list)
                break
        self.weight_list.append(self.weight_times)
        self.weight_times = 0

    def count_restart_times(self, req_list):
        self.restart_times += len(req_list)
        for each_data in req_list:
            if each_data["login_reason"] == 0:
                self.restart_reason0_times += 1
            elif each_data["login_reason"] == 1:
                self.restart_reason1_times += 1
            elif each_data["login_reason"] == 4:
                self.restart_reason4_times += 1

    def get_restart_times(self, esn):
        requests_list = self.get_json(30, esn, self.start_time,
                                      self.end_time, "", 1, self.env_type)
        data_list, res_scroll_id = requests_list
        while 1:
            if res_scroll_id != "end":
                self.count_restart_times(data_list)
                requests_list = self.get_json(30, esn, self.start_time,
                                              self.end_time, res_scroll_id, 1, self.env_type)
                data_list, res_scroll_id = requests_list
            elif res_scroll_id == "end":
                self.count_restart_times(data_list)
                break
        self.restart_times_list.append(self.restart_times)
        self.restart_reason0_list.append(self.restart_reason0_times)
        self.restart_reason1_list.append(self.restart_reason1_times)
        self.restart_reason4_list.append(self.restart_reason4_times)
        self.restart_times = 0
        self.restart_reason0_times = 0
        self.restart_reason1_times = 0
        self.restart_reason4_times = 0

    def count_alarm_info(self, req_list):
        self.alarm_times += len(req_list)
        for each_data in req_list:
            alarm_info = json.loads(each_data["ext"])["告警内容"][0]
            if alarm_info["id"] == 340:
                self.alarm_340_times += 1

    def get_alarm_info(self, esn):
        requests_list = self.get_json(30, esn, self.start_time,
                                      self.end_time, "", 4, self.env_type)
        data_list, res_scroll_id = requests_list
        while 1:
            if res_scroll_id != "end":
                self.count_alarm_info(data_list)
                requests_list = self.get_json(30, esn, self.start_time,
                                              self.end_time, res_scroll_id, 4, self.env_type)
                data_list, res_scroll_id = requests_list
            elif res_scroll_id == "end":
                self.count_alarm_info(data_list)
                break
        self.alarm_times_list.append(self.alarm_times)
        self.alarm_times = 0
        self.alarm_340_times_list.append(self.alarm_340_times)
        self.alarm_340_times = 0

    def get_excel_dict4(self):
        self.data_dict4["ECU编号"] = self.esn_list
        self.data_dict4["开始时间"] = ((self.start_time + ",") * len(self.esn_list)).split(",")[:-1]
        self.data_dict4["结束时间"] = ((self.end_time + ",") * len(self.esn_list)).split(",")[:-1]
        for esn in self.esn_list:
            self.get_alarm_info(esn)
        self.data_dict4["报警总数"] = self.alarm_times_list
        self.data_dict4["340次数"] = self.alarm_340_times_list
        return self.data_dict4

    def get_respect_pulse(self):
        d1 = self.start_time_ts
        d2 = self.end_time_ts
        self.respect_pulse_times = int((d2 - d1) / 120)
        for i in range(len(self.esn_list)):
            self.respect_pulse.append(self.respect_pulse_times)
        return self.respect_pulse

    def get_excel_dict1(self):
        self.data_dict1["ECU编号"] = self.esn_list
        self.data_dict1["开始时间"] = ((self.start_time + ",") * len(self.esn_list)).split(",")[:-1]
        self.data_dict1["结束时间"] = ((self.end_time + ",") * len(self.esn_list)).split(",")[:-1]
        for esn in self.esn_list:
            self.get_restart_times(esn)
            self.get_heart_times(esn)
            self.get_gps_times(esn)
            self.get_weight_times(esn)
        self.data_dict1["重启次数"] = self.restart_times_list
        self.data_dict1["reason为0次数"] = self.restart_reason0_list
        self.data_dict1["reason为1次数"] = self.restart_reason1_list
        self.data_dict1["reason为4次数"] = self.restart_reason4_list
        self.data_dict1["心跳包个数"] = self.heart_list
        self.data_dict1["预期心跳包个数"] = self.get_respect_pulse()
        self.data_dict1["实际心跳包占比"] = [str(round((i / self.respect_pulse_times) * 100, 4)) + '%' for i in
                                      self.heart_list]
        self.data_dict1["定位包个数"] = self.gps_list
        self.data_dict1["重量包个数"] = self.weight_list
        return self.data_dict1

    def count_latitude_and_longitude_data(self, req_list):
        for i in req_list:
            try:
                req_time = json.loads(i["ext"])["createTime"]
                longitude = json.loads(i["ext"])["经度"]
                latitude = json.loads(i["ext"])["纬度"]
                ecu_sn = str(json.loads(i["ecu_sn"]))
                gps_satellite = json.loads(i["ext"])["GPS锁定卫星数"]
                gsm = json.loads(i["ext"])["GSM信号强度"]
                satellite = json.loads(i["ext"])["GPS 最强5颗卫星编号及强度"]
                satellite0 = satellite[0]["snr"]
                satellite1 = satellite[1]["snr"]
                satellite2 = satellite[2]["snr"]
                satellite3 = satellite[3]["snr"]
                satellite4 = satellite[4]["snr"]
                loctation = json.loads(i["ext"])["定位状态"]
                loctation_type = json.loads(i["ext"])["定位类型"]
                azimuth = json.loads(i["ext"])["方位角"]
                speed = json.loads(i["ext"])["霍尔速度"]
                # mobile_network_type = json.loads(i["ext"])["移动网络类型"]
                self.esn_data_list.append(ecu_sn)
                self.longitude_data_list.append(round(longitude, 8))
                self.latitude_data_list.append(round(latitude, 8))
                self.req_time_data_list.append(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(req_time / 1000)))
                self.gps_satellite_list.append(gps_satellite)
                self.gsm_list.append(gsm)
                self.satellite0_list.append(satellite0)
                self.satellite1_list.append(satellite1)
                self.satellite2_list.append(satellite2)
                self.satellite3_list.append(satellite3)
                self.satellite4_list.append(satellite4)
                self.location_list.append(loctation)
                self.location_type_list.append(loctation_type)
                self.azimuth_list.append(azimuth)
                self.speed_list.append(speed)
                # self.mobile_network_type_list.append(mobile_network_type)
            except:
                pass

    def get_latitude_and_longitude_dict(self, esn):
        requests_list = self.get_json(30, esn, self.start_time,
                                      self.end_time, "", 2, self.env_type)
        data_list, res_scroll_id = requests_list
        while 1:
            if res_scroll_id != "end":
                self.count_latitude_and_longitude_data(data_list)
                requests_list = self.get_json(30, esn, self.start_time,
                                              self.end_time, res_scroll_id, 2, self.env_type)
                data_list, res_scroll_id = requests_list
            elif res_scroll_id == "end":
                self.count_latitude_and_longitude_data(data_list)
                break

    def get_excel_dict2(self):
        for esn in self.esn_list:
            self.get_latitude_and_longitude_dict(esn)

        self.data_dict2["ECU编号"] = self.esn_data_list
        self.data_dict2["经度"] = self.longitude_data_list
        self.data_dict2["维度"] = self.latitude_data_list
        self.data_dict2["steCreateTime"] = self.req_time_data_list
        self.data_dict2["GPS锁定卫星数"] = self.gps_satellite_list
        self.data_dict2["GSM信号强度"] = self.gsm_list
        self.data_dict2["卫星1"] = self.satellite0_list
        self.data_dict2["卫星2"] = self.satellite1_list
        self.data_dict2["卫星3"] = self.satellite2_list
        self.data_dict2["卫星4"] = self.satellite3_list
        self.data_dict2["卫星5"] = self.satellite4_list
        self.data_dict2["定位状态"] = self.location_list
        self.data_dict2["定位类型"] = self.location_type_list
        self.data_dict2["方位角"] = self.azimuth_list
        self.data_dict2["霍尔速度"] = self.speed_list
        # self.data_dict2["移动网络类型"] = self.mobile_network_type_list
        
        return self.data_dict2

    def write_excel_pd(self):
        self.update_token(self.env_type)
        writer = pd.ExcelWriter("test/GPS数据.xlsx")
        df = pd.DataFrame(self.get_excel_dict1())
        df = df.set_index("ECU编号")
        df.to_excel(excel_writer=writer, sheet_name="登录信息")

        df = pd.DataFrame(self.get_excel_dict2())
        df = df.set_index("ECU编号")
        df.to_excel(excel_writer=writer, sheet_name="经纬度信息")

        self.data_dict3["ECU编号"] = self.esn_list
        self.data_dict3["定位包总数"] = self.gps_list
        

        for i in self.gps_list:
            # try:
            #     self.type_2g_account.append(str(round((self.mobile_network_type_list[:i].count("TYPE_2G") / len(self.mobile_network_type_list[:i])) * 100, 4)) + "%")
            # except:
            #     self.type_2g_account.append("0")
            # try:
            #     self.type_4g_account.append(str(round((self.mobile_network_type_list[:i].count("TYPE_4G") / len(self.mobile_network_type_list[:i])) * 100, 4)) + "%")
            # except:
            #     self.type_4g_account.append("0")
            try:
                self.location_0_account.append(
                    str(round((self.location_list[:i].count(0) / len(self.location_list[:i])) * 100, 4)) + "%")
            except:
                self.location_0_account.append("0")
            try:
                self.location_1_account.append(
                    str(round((self.location_list[:i].count(1) / len(self.location_list[:i])) * 100, 4)) + "%")
            except:
                self.location_1_account.append("0")
            try:
                self.location_2_account.append(
                    str(round((self.location_list[:i].count(2) / len(self.location_list[:i])) * 100, 4)) + "%")
            except:
                self.location_2_account.append("0")
            try:
                filter_list1 = list(filter(lambda x: True if 0 <= x < 100 else False, self.satellite0_list[:i]))
                self.avg_snr1.append(round(sum(filter_list1) / len(filter_list1), 1))
            except:
                self.avg_snr1.append("0")
            try:
                filter_list2 = list(filter(lambda x: True if 0 <= x < 100 else False, self.satellite1_list[:i]))
                self.avg_snr2.append(round(sum(filter_list2) / len(filter_list2), 1))
            except:
                self.avg_snr2.append("0")
            try:
                filter_list3 = list(filter(lambda x: True if 0 <= x < 100 else False, self.satellite2_list[:i]))
                self.avg_snr3.append(round(sum(filter_list3) / len(filter_list3), 1))
            except:
                self.avg_snr3.append("0")
            try:
                filter_list4 = list(filter(lambda x: True if 0 <= x < 100 else False, self.satellite3_list[:i]))
                self.avg_snr4.append(round(sum(filter_list4) / len(filter_list4), 1))
            except:
                self.avg_snr4.append("0")
            try:
                filter_list5 = list(filter(lambda x: True if 0 <= x < 100 else False, self.satellite4_list[:i]))
                self.avg_snr5.append(round(sum(filter_list5) / len(filter_list5), 1))
            except:
                self.avg_snr5.append("0")
            try:
                self.avg_gsm.append(round(sum(self.gsm_list[:i]) / len(self.gsm_list[:i]), 1))
            except:
                self.avg_gsm.append("0")
            try:
                self.avg_lock_star.append(round(sum(self.gps_satellite_list[:i]) / len(self.gps_satellite_list[:i]), 1))
            except:
                self.avg_lock_star.append("0")

            del self.location_list[:i]
            del self.satellite0_list[:i]
            del self.satellite1_list[:i]
            del self.satellite2_list[:i]
            del self.satellite3_list[:i]
            del self.satellite4_list[:i]
            del self.gsm_list[:i]
            del self.gps_satellite_list[:i]
            del self.mobile_network_type_list[:i]

        for j in range(len(self.avg_snr1)):
            self.avg_snr1_5.append(round(sum([int(self.avg_snr1[j]) + int(self.avg_snr2[j]) + int(
                self.avg_snr3[j]) + int(self.avg_snr4[j]) + int(self.avg_snr5[j])]) / 5, 1))

        self.data_dict3["定位状态0占比"] = self.location_0_account
        self.data_dict3["定位状态1占比"] = self.location_1_account
        self.data_dict3["定位状态2占比"] = self.location_2_account
        # self.data_dict3["网络类型2g占比"] = self.type_2g_account
        # self.data_dict3["网络类型4g占比"] = self.type_4g_account
        self.data_dict3["snr1平均值"] = self.avg_snr1
        self.data_dict3["snr2平均值"] = self.avg_snr2
        self.data_dict3["snr3平均值"] = self.avg_snr3
        self.data_dict3["snr4平均值"] = self.avg_snr4
        self.data_dict3["snr5平均值"] = self.avg_snr5
        self.data_dict3["snr1~5平均值"] = self.avg_snr1_5
        self.data_dict3["GSM平均值"] = self.avg_gsm
        self.data_dict3["锁定卫星数平均值"] = self.avg_lock_star

        df = pd.DataFrame(self.data_dict3)
        df = df.set_index("ECU编号")
        print("准备开始存储信息")
        df.to_excel(excel_writer=writer, sheet_name="GPS信息")

        # df = pd.DataFrame(self.get_excel_dict4())
        # df = df.set_index("ECU编号")
        # df.to_excel(excel_writer=writer, sheet_name="报警包信息")

        writer.save()
        writer.close()


def send(mail_name, subject, file_name):
    msg_from = 'xingfengye@songguo7.com'
    passwd = 'xing2@dong'
    msg_to = ",".join([i + '@songguo7.com' for i in mail_name.split(",")])
    # msg_to = ','.join(['liuxin6@songguo7.com', ])
    # 放入邮件主题
    subject = subject
    # 创建一个带附件的实例
    msg = MIMEMultipart()
    # 放入发件人
    msg['From'] = msg_from
    msg['To'] = msg_to
    msg['Subject'] = Header(subject, 'utf-8')
    msg.attach(MIMEText('数据已导出，请查看附件！', 'plain', 'utf-8'))
    # att = MIMEText(open(self.filename, 'rb').read(), 'base64', 'utf-8')
    # att["Content-Type"] = 'application/octet-stream'
    # att.add_header('Content-Disposition', 'attachment', filename=('gbk', '', name))
    # with open(self.filename, 'rb') as f:
    #     # 设置附件的MIME和文件名，这里是rar类型:
    #     mime = MIMEBase('zip', 'zip', filename=self.filename)
    #     # 加上必要的头信息:
    #     mime.add_header('Content-Disposition', 'attachment', filename=('gbk', '', name))
    #     mime.add_header('Content-ID', '<0>')
    #     mime.add_header('X-Attachment-Id', '0')
    #     # 把附件的内容读进来:
    #     mime.set_payload(f.read())
    #     # 用Base64编码
    # encoders.encode_base64(mime)
    att = MIMEText(open(file_name, 'rb').read(), 'base64', 'utf-8')
    att["Content-Type"] = 'application/octet-stream'
    att.add_header('Content-Disposition', 'attachment', filename=('gbk', '', file_name))
    # att["Content-Disposition"] = 'attachment; filename=("gbk", "", %s)' % name[-1]  # 这里的filename可以任意写，写什么名字，邮件中显示什么名字
    msg.attach(att)

    try:
        # 通过ssl方式发送
        s = smtplib.SMTP_SSL("smtp.songguo7.com")
        # 登录到邮箱
        s.login(msg_from, passwd)
        # 发送邮件：发送方，收件方，要发送的消息
        s.sendmail(msg_from, msg['To'].split(','), msg.as_string())
        print('邮件 %s 发送成功，已发送至%s 请查收!' % (file_name, msg_to))
        logging.info('邮件 %s 发送成功，已发送至%s 请查收!' % (file_name, msg_to))
    except Exception as e:
        print('邮件 %s 发送失败，message: %s .' % (file_name, e))
        logging.error('邮件 %s 发送失败，message: %s .' % (file_name, e))
    finally:
        s.quit()


def main():
    if get_type == "获取压测数据":
        GetStressData(start_time_text, end_time_text, ecu_text, env_type_text).write_excel_pd()
        GetStressData(start_time_text, end_time_text, ecu_text, env_type_text).statistical_account()
        file_name = "压测数据.zip"
        shutil.make_archive("压测数据", "zip", "test")
        send(email_name_text, start_time_text + "-" + end_time_text + " 压测数据", file_name)

    elif get_type == "获取GPS数据（含登录情况）":
        GetGPSData(start_time_text, end_time_text, ecu_text, env_type_text).write_excel_pd()
        file_name = "GPS数据.zip"
        shutil.make_archive("GPS数据", "zip", "test")
        send(email_name_text, start_time_text + "-" + end_time_text + " GPS数据", file_name)

    elif get_type == "获取轨迹数据":
        GetTxtData(start_time_text, end_time_text, ecu_text, env_type_text).write_excel_pd()
        file_name = "轨迹数据.zip"
        shutil.make_archive("轨迹数据", "zip", "test")
        send(email_name_text, start_time_text + "-" + end_time_text + " 轨迹数据", file_name)
